import pandas as pd

fujian2_path = r"..\data\附件2.xlsx"

fujian1_path = r"..\data\附件1.xlsx"

fujian2 = pd.read_excel(fujian2_path, sheet_name=None)

fujian1 = pd.read_excel(fujian1_path, sheet_name=None)

fujian2_sheet1 = fujian2["2023年的农作物种植情况"]

# 预处理1
fujian2_sheet1["种植地块"] = fujian2_sheet1["种植地块"].ffill()

print(fujian2_sheet1)

fujian2_sheet2 = fujian2["2023年统计的相关数据"]

fujian2_sheet2 = fujian2["2023年统计的相关数据"].iloc[:-3]
print(fujian2_sheet2)

fujian1_sheet1 = fujian1["乡村的现有耕地"]
fujian1_sheet1 = fujian1_sheet1.iloc[:, :-1]
print(fujian1_sheet1)


fujian1_sheet2 = fujian1["乡村种植的农作物"]
fujian1_sheet2 = fujian1_sheet2.iloc[:-4, :-1]
fujian1_sheet2 = fujian1_sheet2.ffill()
print(fujian1_sheet2)


# 所有的附件表格初步预处理完成

# 合并地块类型
result_df = fujian2_sheet1.merge(
    fujian1_sheet1, how="left", left_on=["种植地块"], right_on=["地块名称"]
)

result_df.drop(columns=["地块名称"], inplace=True)

# print(result_df.columns)
# print('作物编号' in result_df.columns)
# 地块信息的 df
print(result_df)

result_df["地块类型"] = result_df["地块类型"].str.strip()
print(result_df["地块类型"].unique())

# 添加两列：对应种植地块第一季度和第二季度能种植的所有作物名称
# 也用 \n\n 分隔
result_df["第一季度可种作物"] = ""
result_df["第二季度可种作物"] = ""

# 先维护地块类型和可种植季度的字典，再应用到所有种植地块上

crops_for_lands_jidu1 = {}
crops_for_lands_jidu2 = {}
for idx, row in fujian1_sheet2.iterrows():
    # print(row)
    crop = row["作物名称"]
    lands_for_crop = row["种植耕地"]
    lands_for_crop_list = [
        land.strip() for land in lands_for_crop.split("\n\n") if land.strip() != ""
    ]
    # print(lands_for_crop_list)
    for land in lands_for_crop_list:
        if "第一季" not in land and "第二季" not in land:
            # print(land)
            # 认为只能种第一季度
            crops_for_lands_jidu1[land] = crops_for_lands_jidu1.get(land, []) + [crop]
            # crops_for_lands_jidu2[land] = crops_for_lands_jidu2.get(land, []) + [crop]
        elif "第一季" in land and "第二季" in land:
            land = (
                land.replace("第一季", "")
                .replace("第二季", "")
                .replace("、", "")
                .strip()
            )
            # print(land)
            crops_for_lands_jidu1[land] = crops_for_lands_jidu1.get(land, []) + [crop]
            crops_for_lands_jidu2[land] = crops_for_lands_jidu2.get(land, []) + [crop]
        else:
            if "第一季" in land:
                land = land.replace("第一季", "").strip()
                # print(land)
                crops_for_lands_jidu1[land] = crops_for_lands_jidu1.get(land, []) + [
                    crop
                ]
            if "第二季" in land:
                land = land.replace("第二季", "").strip()
                # print(land)
                crops_for_lands_jidu2[land] = crops_for_lands_jidu2.get(land, []) + [
                    crop
                ]

result_df["第一季度可种作物"] = (
    result_df["地块类型"]
    .map(crops_for_lands_jidu1)
    .apply(lambda x: "\n\n".join(x) if isinstance(x, list) else "")
)
result_df["第二季度可种作物"] = (
    result_df["地块类型"]
    .map(crops_for_lands_jidu2)
    .apply(lambda x: "\n\n".join(x) if isinstance(x, list) else "")
)

# 找到含有 Nan 值的行
nan_rows = result_df[
    result_df[["第一季度可种作物", "第二季度可种作物"]].isnull().any(axis=1)
]
print("含有 NaN 值的行：")
print(nan_rows)
# result_df.to_csv(r'..\A\result_df.csv', index=False)

result_df.drop(
    columns=["作物编号", "作物名称", "作物类型", "种植面积/亩", "种植季次"],
    inplace=True,
)
result_df.drop_duplicates(inplace=True)
result_df.to_csv(r"..\A\result_df.csv", index=False)
